<?php
include_once $_SERVER['DOCUMENT_ROOT'].'/phorm/database/core/ColumnMap.php';

final class MySQLCRUD
{
	const SELF     = '_self';
	const RELATIVE = '_relative';
	
	private static function tablename(&$target)
	{
		return strtolower(get_class($target));
	}
	
	public static function buildCountQuery($objectName)
	{
		$sql = 'SELECT COUNT(*) as `count` ';
		return $sql;
	}
	
	public static function buildSaveQuery(&$target)
	{
		$query = false;
		
		if (!$target->isNew)
		{
			$query.='UPDATE `'.strtolower(get_class($target)).'` SET ';
			foreach ($target->columns as $key=>$value)
			{
				if ($value->isDirty && !($value instanceof IIgnorableColumn) && !($value instanceof IInsertOnlyColumn) && !($value instanceof PrimaryKey))
				{
					$query.= $key.'='.':'.$key.', ';
				}
			}
			// get rid of the last comma
			$query = substr($query, 0, -2);
			
			$query.=' WHERE '.$target->primary_key->column."='".$target->primary_key->value."'";
			
			return $query;
		}
		else
		{
			$query .= 'INSERT INTO `'.strtolower(get_class($target)).'` (';

			foreach ($target->columns as $key=>$value)
			{
				if (!($value instanceof IIgnorableColumn))
				{
					if ($value instanceof PrimaryKey)
					{
						if ($value->auto_increment == false)
							$query.='`'.$key.'`, ';
					}
					else
					{
						$query.='`'.$key.'`, ';
					}
				}
			}
			// get rid of the last comma
			$query = substr($query, 0, -2);
			
			$query.=') VALUES (';
			
			foreach ($target->columns as $key=>$value)
			{
				if (!($value instanceof IIgnorableColumn))
				{
					if ($value instanceof PrimaryKey)
					{
						if ($value->auto_increment == false)
							$query.= ':'.$key.', ';//$value->value.", ";
					}
					else
					{
						$query.= ':'.$key.', ';//$value->value.", ";
					}
				}
			}
			
			// get rid of the last comma
			$query = substr($query, 0, -2);
			
			$query.=')';
			
			return $query;
		}
	}
	
	public static function buildDeleteQuery(&$target, $id = null)
	{
		$query = false;
		//$id = $id == null ? $target->primary_key->value : $id;
		
		try
		{
			$query = 'DELETE FROM `'.strtolower(get_class($target)).'` WHERE ';
			
			foreach($target->columns as $column=>$value)
			{
				if($value->value)
					$query .= $column." = '".$value->value."' AND ";
			}
			
			$query = substr($query, 0, -5);
			
			//if ($id)
			//	$query = 'DELETE FROM `'.strtolower(get_class($target)).'` WHERE '.$target->primary_key->column." = '".$id."'";
			//else
			//	throw new Exception("Warning: Attempt to delete record of type: ".get_class($target).".  No record id was found.");
		}
		catch (Exception $e)
		{
			echo $e->getMessage();
		}
		
		return $query;
	}
	
	public static function dropTable(&$target)
	{
		$config = PhormConfig::getConfig();
		$sql = "DROP TABLE `".strtolower(get_class($target)).'`';
		return $sql;
	}
	
	public static function emptyTable(&$target)
	{
		$config = PhormConfig::getConfig();
		$sql = "TRUNCATE TABLE `".strtolower(get_class($target)).'`';
		return $sql;
	}
	
	public static function createUUIDTrigger(&$table, &$column)
	{
		$tablename = self::tablename($table);
		$sql       = 'CREATE TRIGGER '.$column->column.'_uuid_insert BEFORE INSERT ON `'.$tablename.'` FOR EACH ROW SET NEW.'.$column->column.' = UUID();';
		
		return $sql;
	}
	
	public static function buildTable(&$target)
	{
		$config      = PhormConfig::getConfig();
		$columnMap   = new ColumnMap($config->databaseType);
		$foreignKeys = array();
		$uniqueKeys  = array();
		//$sql = "CREATE TABLE IF NOT EXISTS `".strtolower(get_class($target))."`(\n";
		$sql = "CREATE TABLE IF NOT EXISTS `".self::tablename($target)."`(\n";
		foreach($target->columns as $key=>$obj)
		{
			if ($obj instanceof PrimaryKey)
			{	
				$auto_increment = $obj->auto_increment ? ' auto_increment' : '';
				$sql .= '`'.$key.'` '.$columnMap->{$target->columns[$key]->field_type}.'('.$target->columns[$key]->length.') '.$obj->options.' '.$auto_increment.",\n";
				
			}
			elseif (!($obj instanceof IIgnorableColumn))
			{
				if ($obj instanceof TextArea):
					$sql .= '`'.$key.'` '.$columnMap->{$target->columns[$key]->field_type}.' '.$obj->options.",\n";
				else:
					if ($obj instanceof ForeignKey)
						$foreignKeys[] = $obj;
					
					if($obj->unique)
						$uniqueKeys[]  = $obj;
						
					$sql .= '`'.$key.'` '.$columnMap->{$target->columns[$key]->field_type}.'('.$target->columns[$key]->length.') '.$obj->options.",\n";
				endif;
			}
		}
		if (is_array($target->primary_key))
		{
			$sql .= 'PRIMARY KEY  (';
			foreach($target->primary_key as $key)
			{
				$sql .= '`'.$key->column.'`, ';
			}
			// loase the last comma
			$sql = substr($sql, 0, -2);
			$sql .= ")\n";
		}
		elseif (!empty($target->primary_key))
		{
			$sql .= 'PRIMARY KEY  (`'.$target->primary_key->column."`)\n";
		}
		
		if(sizeof($uniqueKeys)):
			$sql .= ', ';
			foreach($uniqueKeys as $uk)
			{
				$sql .= 'UNIQUE '.$uk->column.'_unique (`'.$uk->column.'`), ';
			}
			$sql = substr($sql, 0, -2);
		endif;
			

		if(sizeof($foreignKeys)):
			$sql .= ', ';
			foreach($foreignKeys as $fk)
			{
				$delete = '';
				$update = '';
				
				if($fk->on_delete)
					$delete = 'ON DELETE '.$fk->on_delete;
				
				if($fk->on_update)
					$update = 'ON UPDATE '.$fk->on_update;
					
				$sql .= " FOREIGN KEY (`{$fk->column}`) REFERENCES `".strtolower(get_class($fk->relation))."` (`{$fk->relation->primary_key->column}`) ".$delete.' '.$update.', ';
			}
			$sql = substr($sql, 0, -2);
		endif;
		
		$sql .=') ENGINE='.$config->databaseEngine.' DEFAULT CHARSET='.$config->charset.';';
		
		return $sql;
	}
	
	public static function addManyToMany(ManyToMany &$object, &$relationship)
	{
		$a = &$object->getModel();
		
		$column1 = strtolower(get_class($a)."_".$a->primary_key->column);
		$column2 = strtolower(get_class($relationship)."_".$relationship->primary_key->column);
		if ($column1 == $column2):
			$column1 .= MySQLCRUD::SELF;
			$column2 .= MySQLCRUD::RELATIVE;
		endif;

		$query = <<<EOD
						INSERT INTO `{$object->pivot_table}` (`$column1`, `$column2`) VALUES ('{$a->primary_key->value}', '{$relationship->primary_key->value}') 
EOD;
	
		return $query;
	}
	
	public static function removeManyToMany(ManyToMany &$object, &$relationship)
	{
		
		$a = &$object->getModel();
		
		$column1 = strtolower(get_class($a)."_".$a->primary_key->column);
		$column2 = strtolower(get_class($relationship)."_".$relationship->primary_key->column);
		if ($column1 == $column2):
			$column1 .= MySQLCRUD::SELF;
			$column2 .= MySQLCRUD::RELATIVE;
		endif;

		$query = <<<EOD
						DELETE FROM `{$object->pivot_table}` WHERE `$column1` = '{$a->primary_key->value}'  AND `$column2` =  '{$relationship->primary_key->value}' 
EOD;
	
		return $query;
	}
	
	public static function createManyToMany(ManyToMany &$object)
	{
		$config = PhormConfig::getConfig();

		$a = get_class($object->getModel());
		$a = new $a;
		$b = new $object->data_type;
		
		/* WARNING  THIS IS NOT SET UP TO HANLDE MULTIPLE PRIMARY KEYS */
		$column1 = strtolower(get_class($a)."_".$a->primary_key->column);
		$column2 = strtolower(get_class($b)."_".$b->primary_key->column);
		if ($column1 == $column2):
			$column1 .= MySQLCRUD::SELF;
			$column2 .= MySQLCRUD::RELATIVE;
		endif;
		$table1  = strtolower(get_class($a));
		$table2  = strtolower(get_class($b));
		$engine  = $config->databaseEngine;
		
		$sql = <<<EOD
					CREATE TABLE IF NOT EXISTS `{$object->pivot_table}` (
					  `$column1` {$a->primary_key->field_type}({$a->primary_key->length}) NOT NULL,
					  `$column2` {$b->primary_key->field_type}({$b->primary_key->length}) NOT NULL,
					  PRIMARY KEY  (`$column1`, `$column2`),
					  FOREIGN KEY (`$column1`) REFERENCES `{$table1}` (`{$a->primary_key->column}`) ON DELETE CASCADE,
					  FOREIGN KEY (`$column2`) REFERENCES `{$table2}` (`{$b->primary_key->column}`) ON DELETE CASCADE,
					  UNIQUE {$column1}_{$column2}_unique (`$column1`, `$column2`) 
					) ENGINE=$engine DEFAULT CHARSET=latin1
EOD;
		$a = null;
		$b = null;
		
		echo $sql;
		return $sql;
	}
	
	
	
	private static function formatTableAndColumnName(&$string)
	{
		$parts = explode('=', $string);
		foreach($parts as &$v)
		{
			if(strpos($v, '->') !== false )
			{
				$tmp = explode('->', $v);
				$tmp[0] = '`'.strtolower($tmp[0]).'`';
				
				$v = implode('.', $tmp);
			}
		}
		
		$string = implode('=', $parts);
	}
	
	public static function appendTables($array)
	{
		$string = ' FROM ';
		
		foreach($array as &$value)
		{
			$table = strtolower($value);
			$string .= '`'.$table.'` `'.$table.'`, ';
		}
		
		return substr($string, 0, -2);
	}
	
	public static function appendSorting($array)
	{
		foreach($array as &$value)
			self::formatTableAndColumnName($value);
		
		return ' ORDER BY '.implode($array, '');
	}
	
	public static function appendFilters($array)
	{
		foreach($array as &$value)
		{
			if(strpos($value, '=') !== false)
				self::formatTableAndColumnName($value);
		}
		
		return ' WHERE '.implode($array, '');
	}
	
	public static function appendLimit($number)
	{
		return ' LIMIT '.$number;
	}
	
	public static function appendOffset($number)
	{
		return ' OFFSET '.$number;
	}
	
	
	public static function buildSelectQuery(&$target, $greedyLoadColumns = NULL)
	{
		
		$sql  = 'SELECT ';
		$sql .= self::appendColumns($target, $greedyLoadColumns);
		$sql .= self::buildTableSQL($target);
		$sql .= self::appendLeftJoins($target, $greedyLoadColumns);
		
		return $sql;
	}
	
	public static function appendLeftJoins(&$target, &$greedyLoadColumns)
	{
		$sql   = '';
		$joins = array();
		$alias = strtolower(get_class($target));
		
		if($greedyLoadColumns)
		{
			foreach($greedyLoadColumns as $key)
			{
				$newAlias = strtolower(get_class($target->columns[$key]->relation));
				$column   = $target->columns[$key]->to_field == null ? $target->columns[$key]->relation->primary_key->column : $target->columns[$key]->to_field;
	
				$joins[] = new LeftJoin(strtolower(get_class($target->columns[$key]->relation)), strtolower($key), $newAlias, $column);
			}
		}
		
		if (count($joins))
		{
			foreach ($joins as $join)
			{
				$sql.= "\nLEFT JOIN ".'`'.$join->table.'` `'.$join->alias.'` ON '.'`'.$alias.'`.'.$join->column.' = '.'`'.$join->alias.'`.'.$join->db_column;
			}
		}
		
		return $sql;
	}
	
	private static function buildTableSQL(&$target)
	{
		$alias = strtolower(get_class($target));
		return ' FROM `'.strtolower(get_class($target)).'` `'.$alias.'`';
	}
	
	public static function appendColumns(&$target, &$greedyLoadColumns)
	{
		$sql    = '';
		$suffix = '';
		$alias  = strtolower(get_class($target));//"a";
		
		foreach ($target->columns as $key=>$value)
		{
			if (!($value instanceof IIgnorableColumn))
			{
				$sql.= '`'.$alias.'`.'.$key.' '.$alias.'_'.$key.', ';
				
				if ($value instanceof ForeignKey)
				{
					if($greedyLoadColumns)
					{
						if(in_array($key, $greedyLoadColumns))
						{
							$newAlias = strtolower(get_class($value->relation));
							
							//$column = $value->to_field == null ? $value->relation->primary_key->column : $value->to_field;
							//$leftJoins[] = new LeftJoin(strtolower(get_class($value->relation)), strtolower($key), $newAlias, $column);
							
							foreach($target->columns[$key]->columns as $foreignColumn => $foreignValue)
							{
								if (!($foreignValue instanceof IIgnorableColumn))
									$suffix .= '`'.$newAlias.'`.'.$foreignColumn.' '.$newAlias.'_'.$foreignColumn.', ';
							}
						}
					}
				}
				/*else	
				{
					$sql.= '`'.$alias.'`.'.$key.' '.$alias.'_'.$key.', ';
				}*/
			}
		}
		
		$sql .= $suffix;
		
		return substr($sql, 0, -2);
	}
	
	/*public static function buildSelectQuery(&$target)
	{
		$leftJoins = array();
		$alias     = strtolower(get_class($target));//"a";
		$query = 'SELECT ';
	
		foreach ($target->columns as $key=>$value)
		{
			if (!($value instanceof IIgnorableColumn))
			{
				if ($value instanceof ForeignKey && $withForeignKeys)
				{
					$query.= '`'.$alias.'`.'.$key.' '.$alias.'_'.$key.', ';
				
					$newAlias = strtolower(get_class($value->relation));
					$column = $value->to_field == null ? $value->relation->primary_key->column : $value->to_field;
				
					$leftJoins[] = new LeftJoin(strtolower(get_class($value->relation)), strtolower($key), $newAlias, $column);
				
					foreach($target->columns[$key]->columns as $foreignColumn => $foreignValue)
					{
						if (!($foreignValue instanceof IIgnorableColumn))
							$query.= '`'.$newAlias.'`.'.$foreignColumn.' '.$newAlias.'_'.$foreignColumn.', ';
					}
				}
				else	
				{
					$query.= '`'.$alias.'`.'.$key.' '.$alias.'_'.$key.', ';
				}
			}
		}
		
		// get rid of the last comma
		$query = substr($query, 0, -2);
		
		$query .= ' FROM `'.strtolower(get_class($target)).'` `'.$alias.'`';
		
		if (count($leftJoins))
		{
			foreach ($leftJoins as $join)
			{
				$query.= "\nLEFT JOIN ".'`'.$join->table.'` `'.$join->alias.'` ON '.'`'.$alias.'`.'.$join->column.' = '.'`'.$join->alias.'`.'.$join->db_column;
			}
		}

		return $query;
	}*/
	
	/* legacy function */
	private static function appendLimitToQuery()
	{
		if($this->offset)
			return ' OFFSET '.$this->offset.' LIMIT '.$this->limit;
		else
			return ' LIMIT '.$this->limit;
	}
	
	public static function buildPrimaryKeyQuery(&$target)
	{
		$query = self::buildSelectQuery($target)."\nWHERE ".'`'.$alias.'`.'.$target->primary_key->column.' = :'.$target->primary_key->column;
		return $query;
	}
	
	
}
?>
